package com.jeesite.modules.stock.dao;

import com.jeesite.common.entity.Page;
import com.jeesite.common.lang.StringUtils;
import com.jeesite.common.utils.ClassUtils;
import com.jeesite.modules.dp.dao.stocktmp.DpTmpjsonDao;
import com.jeesite.modules.scheduler.KcCodeUnLockScheduler;
import com.jeesite.modules.stock.dao.mapper.StockDailyMapper;
import com.jeesite.modules.stock.dto.StockDateRangeRankSearchDTO;
import com.jeesite.modules.stock.dto.StockQueryDTO;
import com.jeesite.modules.stock.dto.StockSearchDTO;
import com.jeesite.modules.stock.entity.StockDailyEntity;
import com.jeesite.modules.stock.service.StockFilterSqlBuilder;
import com.jeesite.modules.utils.NVL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class StockDailyDao {

    @Autowired
    private StockDailyMapper stockDailyMapper;

    @Autowired
    private DpTmpjsonDao dpTmpjsonDao;

    @Value("${file-server.path}")
    protected String fileServerPath;

    @Autowired
    DataSource dataSource;

    private final Logger log = LoggerFactory.getLogger(KcCodeUnLockScheduler.class);

    private JdbcTemplate getJdbcTemplate() {
        return new JdbcTemplate(dataSource);
    }

    static final Map<String, String> conditionRef = new HashMap<String, String>() {
        {
            put("0", ">");
            put("1", "=");
            put("2", "<");
        }
    };

    public Map<String, Object> getStockLatestData(String stockCode) {
        String sql = "select a.* from stock_latest_data as a where a.stock_code=?";
        JdbcTemplate jdbcTempdate = getJdbcTemplate();
        return jdbcTempdate.queryForObject(sql, new String[]{stockCode}, (rs, rowNum) -> {
            ResultSetMetaData metaData = rs.getMetaData();
            Map<String, Object> data = new HashMap<String, Object>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                Object value = rs.getObject(i);
                String columnName = metaData.getColumnName(i);
                data.put(ClassUtils.camelCharset(columnName), value);
            }
            return data;
        });
    }


    /**
     * 构建查询SQL
     *
     * @param stockSearchDTO 查询条件
     * @param countSql       是否是生成count语句
     * @return 生成的查询语句
     */
    private String buildSearchSql(StockSearchDTO stockSearchDTO, boolean countSql) {

        int pageNo = stockSearchDTO.getPageNo();
        int pageSize = stockSearchDTO.getPageSize();

        StockFilterSqlBuilder stockFilterSqlBuilder = new StockFilterSqlBuilder(dpTmpjsonDao);

        String where = stockFilterSqlBuilder.buildTiaoJianWhere(stockSearchDTO.getTiaojianList());

        if (!StringUtils.isBlank(where)) {
            where = " and " + where;
        }

        if (!StringUtils.isBlank(stockSearchDTO.getStockCode())) {
            where += " and (a.stock_code like '" + stockSearchDTO.getStockCode() + "%' "
                    + "or a.stock_name like '%" + stockSearchDTO.getStockCode() + "%' "
                    + "or b.stock_name_pinyin like '" + stockSearchDTO.getStockCode().toUpperCase() + "%')";
        }
        if (!StringUtils.isBlank(stockSearchDTO.getSectorId())) {
            where += " and sc.sector_id=" + stockSearchDTO.getSectorId();
        }
        if (!StringUtils.isBlank(stockSearchDTO.getMaxSectorRsName())) {
            where += " and a.max_sector_rs_name='" + stockSearchDTO.getMaxSectorRsName() + "'";
        }

        int offset = (pageNo - 1) * pageSize;
        String order = StringUtils.isBlank(stockSearchDTO.getOrder()) ? "desc" : stockSearchDTO.getOrder();
        String orderBy = StringUtils.isBlank(stockSearchDTO.getOrderBy()) ? "rise_and_fall"
                : stockSearchDTO.getOrderBy();

        if ("industry".equalsIgnoreCase(orderBy)) {
            orderBy = "b.`" + orderBy + "`";
        } else {
            orderBy = "a.`" + orderBy + "`";
        }

        String columns = countSql ? "count(a.id)" : "a.*,b.industry,lo.logo";

        String sql = "select " + columns + " from stock_latest_data a "
                + "left join stocks_informations b on a.stock_code=b.stock_code ";
        if (!StringUtils.isBlank(stockSearchDTO.getSectorId())) {
            sql += "left join stock_sectors sc on a.stock_code=sc.stock_code ";
        }
        if (!StringUtils.isBlank(stockSearchDTO.getExtraConditions())) {
            where += " and " + stockSearchDTO.getExtraConditions();
        }

        sql += "left join stock_logos lo on a.stock_code=lo.stock_code ";
        sql += "where a.`close` is not null " + where;

        if (!countSql) {
            sql += " order by " + orderBy + " " + order + " limit " + offset
                    + ", " + pageSize;
        }
        if (sql.toLowerCase().contains("delete ")
                || sql.toLowerCase().contains("update ")
                || sql.toLowerCase().contains("alter ")
                || sql.toLowerCase().contains("drop ")
                || sql.toLowerCase().contains("truncate ")) {
            log.error("选股筛选SQL中包含非法语句, SQL: {}", sql);
            return null;
        }
        return sql;
    }

    /**
     * 查询股票日线数据
     *
     * @return
     */
    public List<Map<String, Object>> findList(StockSearchDTO stockSearchDTO) {
        JdbcTemplate jdbcTempdate = getJdbcTemplate();
        return jdbcTempdate.query(buildSearchSql(stockSearchDTO, false),
                (rs, rowNum) -> {
                    ResultSetMetaData metaData = rs.getMetaData();
                    Map<String, Object> data = new HashMap<String, Object>();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        Object value = rs.getObject(i);
                        String columnName = metaData.getColumnName(i);
                        data.put(ClassUtils.camelCharset(columnName), value);
                    }
                    return data;
                });

    }

    /**
     * 查询总记录数
     *
     * @param stockSearchDTO
     * @return
     */
    public Integer findCount(StockSearchDTO stockSearchDTO) {
        JdbcTemplate jdbcTempdate = getJdbcTemplate();
        String sql = buildSearchSql(stockSearchDTO, true);
        return jdbcTempdate.queryForObject(sql, Integer.class);
    }

    /**
     * 查询股票区间排名
     *
     * @return
     */
    public Page<Map<String, Object>> findDateRangeRank(StockDateRangeRankSearchDTO searchDTO) {
        String columns = """
                a.id,a.stock_code,a.stock_name, a.close, lo.logo,
                (select (c2.close - c1.close)*100/c1.close mix from
                  (select * from stock_daily_data where date='#startDate' and stock_code=a.stock_code) as c1,
                  (select * from stock_daily_data where date='#endDate' and stock_code=a.stock_code) as c2
                ) as rise_and_fall
                """;
        String sql = """
                select #columns
                from stock_daily_data a left join stocks_informations si on a.stock_code=si.stock_code
                left join stock_logos lo on a.stock_code=lo.stock_code
                """;
        if (searchDTO.getSectorId() != null && searchDTO.getSectorId() > -1) {
            sql += "left join stock_sectors sc on a.stock_code=sc.stock_code ";
        }
        sql += "where a.date='#endDate' #keyword #sectorId order by rise_and_fall desc ";

        sql = sql.replaceAll("#startDate", searchDTO.getStartDate())
                .replaceAll("#endDate", searchDTO.getEndDate());
        if (!StringUtils.isBlank(searchDTO.getKeyword())) {
            sql = sql.replaceAll("#keyword", " and (a.stock_code like '" + searchDTO.getKeyword() + "%' "
                    + "or si.stock_name_pinyin like '" + searchDTO.getKeyword() + "%')");
        } else {
            sql = sql.replaceAll("#keyword", "");
        }
        if (searchDTO.getSectorId() != null && searchDTO.getSectorId() > -1) {
            sql = sql.replaceAll("#sectorId", "and sc.sector_id=" + searchDTO.getSectorId());
        } else {
            sql = sql.replaceAll("#sectorId", "");
        }
        String querySql = sql.replaceAll("#columns", columns.replaceAll("#startDate", searchDTO.getStartDate())
                .replaceAll("#endDate", searchDTO.getEndDate()));

        String countSql = sql.replaceAll("#columns", "count(1)");
        int pageNo = searchDTO.getPageNo() == null ? 1 : searchDTO.getPageNo();
        int pageSize = searchDTO.getPageSize() == null ? 12 : searchDTO.getPageSize();
        querySql += "limit " + ((pageNo - 1) * pageSize) + "," + pageSize;

        JdbcTemplate jdbcTempdate = getJdbcTemplate();
        List<Map<String, Object>> list = jdbcTempdate.query(querySql,
                (rs, rowNum) -> {
                    ResultSetMetaData metaData = rs.getMetaData();
                    Map<String, Object> data = new HashMap<String, Object>();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        Object value = rs.getObject(i);
                        String columnName = metaData.getColumnName(i);
                        data.put(ClassUtils.camelCharset(columnName), value);
                    }
                    return data;
                });

        Integer total = jdbcTempdate.queryForObject(countSql, Integer.class);

        Page<Map<String, Object>> page = new Page<Map<String, Object>>(pageNo, pageSize);
        page.setCount(NVL.nvl(total, 0));
        page.setList(list);

        return page;
    }

    /**
     * 股票日K线数据
     *
     * @param stockCode 股票代码
     * @return 252天的日K数据
     */
    public List<StockDailyEntity> getKlineDataOfDay(String stockCode, String startDate, String endDate) {
        StockQueryDTO param = new StockQueryDTO();
        param.setStartDate(startDate);
        param.setEndDate(endDate);
        param.setStockCode(stockCode);
        return stockDailyMapper.findStockList(param);
    }

    public List<Map<String, Object>> findRsArrDatas(String stockCode) {
        return stockDailyMapper.findRsArrDatas(stockCode);
    }

    /**
     * @param stockCodes
     * @param startDate
     * @return
     */
    public List<Map<String, Object>> findRsArrDatasOfStockCodes(List<String> stockCodes, String startDate) {
        return stockDailyMapper.findRsArrDatasOfStockCodes(stockCodes, startDate);
    }

    public List<Map<String, Object>> getStockRsStrong(String stockCode, String period) {
        return stockDailyMapper.getStockRsStrong(stockCode, period);
    }

    /**
     * 根据日期查询数量
     *
     * @param date 日期
     * @return 数量
     */
    public Integer findCountOfDate(String date) {
        String sql = "select count(1) from stock_daily_data a where a.date='" + date + "'";
        JdbcTemplate jdbcTempdate = getJdbcTemplate();
        return jdbcTempdate.queryForObject(sql, Integer.class);
    }

    /**
     * 查询股票AI推荐列表
     *
     * @param stockCode 股票代码
     * @return
     */
    public List<Map<String, Object>> getStockAiRecommendStockList(String stockCode) {
        Map<String, Object> data = getStockLatestData(stockCode);
        if (data != null) {
            String maxSectorRsName = String.valueOf(data.get("maxSectorRsName"));
            if (!StringUtils.isBlank(maxSectorRsName)) {
                StockSearchDTO dto = new StockSearchDTO();
                dto.setMaxSectorRsName(maxSectorRsName);
                dto.setOrderBy("rise_and_fall");
                dto.setOrder("desc");
                dto.setPageNo(1);
                dto.setPageSize(1000);
                return this.findList(dto);
            }
        }
        return new ArrayList<>();
    }

}
